Some enterprises may run very large databases. For example, some databases may store hundreds of gigabytes (GB) of data, and in some cases may even store terabytes (TB) of data. Database sizes are expected to continue to grow even larger. Supporting and maintaining these ever expanding databases may be difficult. One tool to simplify support and maintenance is partitioning. Partitioning facilitates managing large volumes of data by providing one logical view to applications while providing multiple physical segments for administration.
Partitions may share the same logical attributes as the table from which they were created. These logical attributes may include column definitions, number of columns, constraint definitions, and so on. Partitioning schemes may include, for example, range partitioning, hash partitioning, list partitioning, and so on. Range partitioning may map rows to partitions based on ranges of column values. Date ranges, age ranges, GPA ranges, and so on are commonly used in range partitioning. Hash partitioning maps rows based on a hash function. This may be useful when range partitioning is unavailable. List partitioning may be implemented by providing an explicit list of which rows are to be placed in which partitions.
Partitioning is not restricted to data tables. Indexes may also be partitioned. A local index may be constructed so that it reflects the structure of an underlying table. Therefore, it may be equi-partitioned with its underlying table and may be partitioned on the same columns as its underlying table.
Partitioning allows large database tables and indexes to be decomposed into smaller, more manageable pieces called partitions. A database management system (DBMS) may allow structured query language (SQL) queries and data manipulation language (DML) statements to access these partitions without being modified. A DBMS may also allow data definition language (DDL) statements to access and manipulate individual partitions rather than entire tables or indexes. Thus, for at least SQL, DML and DDL, partitioning may be transparent.
Partitioning may also be transparent to applications. Applications that manage large volumes of data (e.g., online transaction processing (OLTP) systems, data warehousing systems) may benefit from improved performance, manageability, and availability when partitioned. Thus, given a set of database tables related by referential constraints, a user may wish to equi-partition the tables. An equi-partitioned table is a partitioned table having the same number of partitions as an original table, where a row in the equi-partitioned table is mapped to the partition corresponding to the partition containing its parent row (with respect to the referential constraint) in the original table. Conventionally, equi-partitioning a table required replicating the partitioning key across all tables. Conventionally, equi-partitioning a table also required explicitly and/or manually maintaining the equi-partitioning across partition maintenance operations (PMO)s (e.g., alter table split partition). Conventionally, equi-partitioning a table also required explicitly and/or manually maintaining the equi-partitioning in the face of row migrations between partitions in the original table.
The next two paragraphs provide a review of some basic database concepts. A database may be considered to be a structured collection of inter-related data organized into tables that are stored in a physical location with specific table names and specific column names. A database schema may define objects that are represented in the database. This definition may include a structural description of facts held in the database. Database tables may be accessed using a query language (e.g., SQL). The result of a query may be stored as a logical table referred to as a view. A stored procedure is a group of SQL statements that are stored together as a named set in a database. The named set can be executed as a group by a DBMS. A stored procedure may populate a view. Triggers are stored procedures that may execute automatically upon the occurrence of a certain condition. For example, when a row is added to a database table a trigger may execute. Similarly, when a row is deleted or modified, a trigger may execute.
A constraint may restrict the type of data that can be stored in a database table and/or in a field in a database table. For example, a constraint may require that a certain field be NOT NULL, meaning it must have a value. Another example constraint may require that a certain field contain only upper case letters. Constraints may be associated with keys. For example, a primary key constraint may require that no row have a duplicate value in the primary key. A foreign key is a reference to another key in another table. A foreign key may be used to declare referential integrity constraints. For example, a referential integrity constraint may assert that the value of a foreign key must be taken from a primary key in another table.